/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.*;


/**
 *
 * @author Trang
 */
public class StaffDAO {
    private Connection con;

    public StaffDAO() {
        con = DBFactory.createConnection("onlineshopping");
    }
   
    public List<Staff> getAllStaff()
    {
        List<Staff> result = new ArrayList<Staff>();
        try
        {
            String sql = "SELECT * FROM staff";
            PreparedStatement ps = con.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next())
            {
                Integer idStaff = rs.getInt("idStaff");
                String email = rs.getString("email");
                String loginName = rs.getString("loginName");
                String password = rs.getString("password");
                String firstname = rs.getString("firstname");
                String lastname = rs.getString("lastname");
                String address = rs.getString("address");
                Date dob = rs.getDate("dob");
                String role = rs.getString("role");
                //String role = rs.getString("role");
                Date openTime = rs.getDate("openTime");
                Date closeTime = rs.getDate("closeTime");
                
                Staff staff = new Staff(idStaff, role, email, loginName, password, firstname, lastname, address, dob, openTime, closeTime);
                        //(idStaff, role, email, loginName, password, firstname, lastname, address, dob, openTime, closeTime);
                        //(idStaff, email, firstname, lastname, address, dob, password, loginName);//new Staff();
                        //new Staff(idStaff, email, loginName, password, firstname, lastname);
                
                result.add(staff);
            }
        }
        catch(Exception e)
        {
            
        }
        return result;
    }
    
    public Staff getStaffByStaffLoginName(String sLoginName)
    {
        Staff result = new Staff();
        try
        {
            String sql = "SELECT * FROM staff WHERE loginName = ?";
            
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setString(1, sLoginName);
            
            ResultSet rs = ps.executeQuery();
            
            if(rs.next())
            {
                Integer idStaff = rs.getInt("idStaff");
                String email = rs.getString("email");
                String loginName = rs.getString("loginName");
                String password = rs.getString("password");
                String firstname = rs.getString("firstname");
                String lastname = rs.getString("lastname");
                String address = rs.getString("address");
                Date dob = rs.getDate("dob");
                Date openTime = rs.getDate("openTime");
                Date closeTime = rs.getDate("closeTime");
                
                result =   new Staff(idStaff, email, loginName, password, firstname, lastname);
                        //new Staff(idStaff, email, loginName, password, firstname, lastname, dob, openTime);
            }
            
        return result;
        }
        catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }
    
    public Staff getStaffByStaffId(Integer sid)
    {
        Staff result = new Staff();
        try
        {
            String sql = "SELECT * FROM staff WHERE idStaff = ?";
            
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, sid);
            
            ResultSet rs = ps.executeQuery();
            
            if(rs.next())
            {
                Integer idStaff = rs.getInt("idStaff");
                String email = rs.getString("email");
                String loginName = rs.getString("loginName");
                String password = rs.getString("password");
                String firstname = rs.getString("firstname");
                String lastname = rs.getString("lastname");
                String address = rs.getString("address");
                Date dob = rs.getDate("dob");
                Date openTime = rs.getDate("openTime");
                Date closeTime = rs.getDate("closeTime");
                
                result =   new Staff(idStaff, email, loginName, password, firstname, lastname);
                        //new Staff(idStaff, email, loginName, password, firstname, lastname, dob, openTime);
            }
            
        return result;
        }
        catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }
    
    public Staff getStaffByStaffRole(String role)
    {
        Staff result = new Staff();
        try
        {
            String sql = "SELECT * FROM staff WHERE role = ?";
            
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setString(1, role);
            
            ResultSet rs = ps.executeQuery();
            
            if(rs.next())
            {
                Integer idStaff = rs.getInt("idStaff");
                String email = rs.getString("email");
                String loginName = rs.getString("loginName");
                String password = rs.getString("password");
                String firstname = rs.getString("firstname");
                String lastname = rs.getString("lastname");
                String address = rs.getString("address");
                Date dob = rs.getDate("dob");
                String staffrole = rs.getString("role");
                Date openTime = rs.getDate("openTime");
                Date closeTime = rs.getDate("closeTime");
                
                result = 
                        new Staff(idStaff,staffrole, email, loginName, password, firstname, lastname, address, dob, openTime, closeTime);
                        //Staff(idStaff, email, loginName, password, firstname, lastname, dob, openTime);
            }
        }
        catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }
    
     public boolean updateProfile(Customer customer){
        boolean result = false;
        
        try {
            String sqlst = "UPDATE customer "
                    + "SET address = ?, "
                    + "phone = ?, "
                    + "email = ? "
                    + "WHERE idCustomer = ?";
            
            PreparedStatement ps = con.prepareStatement(sqlst);
            
            ps.setString(1, customer.getAddress());
            ps.setString(2, customer.getPhone());
            ps.setString(3, customer.getEmail());
            ps.setInt(4, customer.getIdCustomer());
            System.out.println(sqlst);
            ps.executeUpdate();
            
            result = true;
        } catch (SQLException ex) {
            Logger.getLogger(AuthenticationDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return result;
    }
    public boolean updateStaff(Staff staff)
    {
        boolean result = false;
        try
        {
            String sql = "UPDATE staff SET "
                    +"firstname=?, lastname=?, address=?, dob=?, "
                    +"email=? where idStaff=?";
            PreparedStatement ps = con.prepareStatement(sql);
            
            
            //ps.setString(1,staff.getRole());
            ps.setString(1,staff.getFirstname());
            ps.setString(2, staff.getLastname());
            ps.setString(3, staff.getAddress());
            ps.setDate(4, new java.sql.Date (staff.getDob().getTime()));
            ps.setString(5, staff.getEmail());
           
            ps.setInt(6,staff.getIdStaff());
            
            System.out.println(result);
            ps.executeUpdate();
            result = true;
        }
        catch(Exception e)
        {
            
        }
        System.out.println(result);
        return result;
    }
    
    public boolean updatePasswordStaff(Staff staff)
    {
        boolean result = false;
        try
        {
            String sql = "UPDATE staff SET "
                    +"password=? "
                    +"where idStaff=?";
            PreparedStatement ps = con.prepareStatement(sql);
            
            
            //ps.setString(1,staff.getRole());
            ps.setString(1,staff.getPassword());
           
            ps.setInt(2,staff.getIdStaff());
            
            System.out.println(result);
            ps.executeUpdate();
            result = true;
        }
        catch(Exception e)
        {
            
        }
        System.out.println(result);
        return result;
    }
    
    public boolean InsertStaff(Staff staff) {
        boolean result = false;

        try {
            //insert member account 1st
            
            SimpleDateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
            Date current = new Date();
            String sqlst1 = "INSERT INTO staff(email, loginName, firstname, lastname,address,password,openTime,dob, role) "
                    + "VALUE(?, ?, ?, ?, ?, ?, ?, ?, ?)";

            PreparedStatement ps = con.prepareStatement(sqlst1);
            ps.setString(1, staff.getEmail());
            ps.setString(2, staff.getLoginName());
            ps.setString(3, staff.getFirstname());
            ps.setString(4, staff.getLastname());
            ps.setString(5, staff.getAddress());
            //ps.setString(6, staff.getRole());
            ps.setString(6, staff.getPassword());
            ps.setDate(7, new java.sql.Date (current.getTime()));
            ps.setDate(8, new java.sql.Date (staff.getDob().getTime()));
            ps.setString(9, "administrator");
//            ps.setDate(9, new java.sql.Date (staff.getDob().getTime()));
            
            ps.executeUpdate();
            result = true;
        } catch (SQLException ex) {
            Logger.getLogger(AuthenticationDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }
    
    public Integer getLastIdStaff()
    {
        Integer result = 0;
         try {
            String sqlst = "SELECT idStaff FROM staff ORDER BY idStaff DESC"                
                    + " LIMIT 1";
            PreparedStatement ps = con.prepareStatement(sqlst);
            

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                result  = rs.getInt("idStaff");             
                //result.add(new Product(idProduct, name, summary, details, brand, others, unitPrice, stockQuantity, sale, imageurl));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

         return result;
    }
    
}
